"""Base service class with common CRUD operations."""

from __future__ import annotations

from typing import Any, Generic, TypeVar

from sqlalchemy import delete, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.db.base_class import Base

# Generic type for model class
ModelType = TypeVar("ModelType", bound=Base)


class BaseService(Generic[ModelType]):
    """Base service with common CRUD operations.

    Usage:
        ```python
        class UserService(BaseService[User]):
            def __init__(self, session: AsyncSession):
                super().__init__(session, User)

            # Add custom methods here
            async def get_by_email(self, email: str) -> User | None:
                return await self.get_one(filters={"email": email})
        ```
    """

    def __init__(self, session: AsyncSession, model: type[ModelType]):
        """Initialize service with database session and model class.

        Args:
            session: SQLAlchemy async session
            model: SQLAlchemy model class
        """
        self.session = session
        self.model = model

    async def get_by_id(self, id: int) -> ModelType | None:
        """Get single record by ID.

        Args:
            id: Primary key value

        Returns:
            Model instance or None if not found
        """
        result = await self.session.execute(select(self.model).where(self.model.id == id))
        return result.scalar_one_or_none()

    async def get_one(self, filters: dict[str, Any] | None = None) -> ModelType | None:
        """Get single record with optional filters.

        Args:
            filters: Dictionary of field->value pairs for filtering

        Returns:
            Model instance or None if not found
        """
        stmt = select(self.model)
        if filters:
            for key, value in filters.items():
                stmt = stmt.where(getattr(self.model, key) == value)
        result = await self.session.execute(stmt)
        return result.scalar_one_or_none()

    async def get_many(
        self,
        skip: int = 0,
        limit: int = 20,
        filters: dict[str, Any] | None = None,
        order_by: str | None = None,
        order_desc: bool = True,
    ) -> tuple[list[ModelType], int]:
        """Get multiple records with pagination and filtering.

        Args:
            skip: Number of records to skip (offset)
            limit: Maximum number of records to return
            filters: Dictionary of field->value pairs for filtering
            order_by: Field name to sort by (defaults to "created_at" if available)
            order_desc: Sort in descending order (default True)

        Returns:
            Tuple of (list of records, total count)
        """
        stmt = select(self.model)

        # Apply filters
        if filters:
            for key, value in filters.items():
                if value is not None:  # Skip None values
                    stmt = stmt.where(getattr(self.model, key) == value)

        # Count total matching records
        count_stmt = select(func.count()).select_from(stmt.subquery())
        total_result = await self.session.execute(count_stmt)
        total = total_result.scalar_one()

        # Apply ordering
        if order_by:
            order_field = getattr(self.model, order_by)
        elif hasattr(self.model, "created_at"):
            order_field = self.model.created_at
        else:
            order_field = self.model.id

        if order_desc:
            stmt = stmt.order_by(order_field.desc())
        else:
            stmt = stmt.order_by(order_field.asc())

        # Apply pagination
        stmt = stmt.offset(skip).limit(limit)

        # Execute query
        result = await self.session.execute(stmt)
        records = result.scalars().all()

        return list(records), total

    async def create(self, **data: Any) -> ModelType:
        """Create new record.

        Args:
            **data: Field values for the new record

        Returns:
            Created model instance
        """
        instance = self.model(**data)
        self.session.add(instance)
        await self.session.flush()
        await self.session.refresh(instance)
        return instance

    async def update(self, id: int, **data: Any) -> ModelType | None:
        """Update existing record by ID.

        Args:
            id: Primary key value
            **data: Field values to update

        Returns:
            Updated model instance or None if not found
        """
        instance = await self.get_by_id(id)
        if not instance:
            return None

        for key, value in data.items():
            if hasattr(instance, key):
                setattr(instance, key, value)

        await self.session.flush()
        await self.session.refresh(instance)
        return instance

    async def delete(self, id: int) -> bool:
        """Delete record by ID.

        Args:
            id: Primary key value

        Returns:
            True if deleted, False if not found
        """
        instance = await self.get_by_id(id)
        if not instance:
            return False

        await self.session.delete(instance)
        await self.session.flush()
        return True

    async def delete_many(self, ids: list[int]) -> int:
        """Delete multiple records by IDs.

        Args:
            ids: List of primary key values

        Returns:
            Number of records deleted
        """
        if not ids:
            return 0

        stmt = delete(self.model).where(self.model.id.in_(ids))
        result = await self.session.execute(stmt)
        await self.session.flush()
        return result.rowcount or 0

    async def count(self, filters: dict[str, Any] | None = None) -> int:
        """Count records with optional filters.

        Args:
            filters: Dictionary of field->value pairs for filtering

        Returns:
            Total count of matching records
        """
        stmt = select(func.count()).select_from(self.model)
        if filters:
            for key, value in filters.items():
                if value is not None:
                    stmt = stmt.where(getattr(self.model, key) == value)

        result = await self.session.execute(stmt)
        return result.scalar_one()

    async def exists(self, id: int) -> bool:
        """Check if record exists by ID.

        Args:
            id: Primary key value

        Returns:
            True if exists, False otherwise
        """
        stmt = select(func.count()).select_from(self.model).where(self.model.id == id)
        result = await self.session.execute(stmt)
        count = result.scalar_one()
        return count > 0
